﻿/**  版本信息模板在安装目录下，可自行修改。
* ST_Shop.cs
*
* 功 能： N/A
* 类 名： ST_Shop
*
* Ver    变更日期             负责人  变更内容
* ───────────────────────────────────
* V0.01  2015/1/15 10:26:13   N/A    初版
*
* Copyright (c) 2012 Maticsoft Corporation. All rights reserved.
*┌──────────────────────────────────┐
*│　此技术信息为本公司机密信息，未经本公司书面同意禁止向第三方披露．　│
*│　版权所有：动软卓越（北京）科技有限公司　　　　　　　　　　　　　　│
*└──────────────────────────────────┘
*/
using DBUtil;
using Function;
using Model;
using Model.Permission;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DAL
{
	/// <summary>
	/// 数据访问类:ST_Shop
	/// </summary>
	public partial class ST_ShopDAL
	{
         DbHelperSQLP DbHelperSQL = new DbHelperSQLP(PubConstant.JHShop);
         public ST_ShopDAL()
		{}
         #region  BasicMethod
         /// <summary>
         /// 是否存在该记录
         /// </summary>
         public bool Exists(int ID)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select count(1) from ST_Shop");
             strSql.Append(" where ID=@ID");
             SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
             parameters[0].Value = ID;

             return DbHelperSQL.Exists(strSql.ToString(), parameters);
         }


         /// <summary>
         /// 增加一条数据
         /// </summary>
         public int Add(ST_Shop model)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("insert into ST_Shop(");
             strSql.Append("ApplyID,STitle,Region,Code,KeyID,POSID,Bail,ShopType,PriceRole,Address,Legal,LegalTel,LegalIDCardURL,Captain,CaptainIDCardURL,CaptainTel,Email,LicenceCode,LicenceURL,Permit,PermitURL,WinePermit,WinePermitURL,JoinMoney,CreateTime,ShopState,Note,FacePhoto,ShopHours)");
             strSql.Append(" values (");
             strSql.Append("@ApplyID,@STitle,@Region,@Code,@KeyID,@POSID,@Bail,@ShopType,@PriceRole,@Address,@Legal,@LegalTel,@LegalIDCardURL,@Captain,@CaptainIDCardURL,@CaptainTel,@Email,@LicenceCode,@LicenceURL,@Permit,@PermitURL,@WinePermit,@WinePermitURL,@JoinMoney,@CreateTime,@ShopState,@Note,@FacePhoto,@ShopHours)");
             strSql.Append(";select @@IDENTITY");
             SqlParameter[] parameters = {
					new SqlParameter("@ApplyID", SqlDbType.Int,4),
					new SqlParameter("@STitle", SqlDbType.NVarChar,64),
					new SqlParameter("@Region", SqlDbType.VarChar,6),
					new SqlParameter("@Code", SqlDbType.VarChar,14),
					new SqlParameter("@KeyID", SqlDbType.VarChar,14),
					new SqlParameter("@POSID", SqlDbType.VarChar,20),
					new SqlParameter("@Bail", SqlDbType.Decimal,5),
					new SqlParameter("@ShopType", SqlDbType.TinyInt,1),
					new SqlParameter("@PriceRole", SqlDbType.Int,4),
					new SqlParameter("@Address", SqlDbType.NVarChar,256),
					new SqlParameter("@Legal", SqlDbType.NVarChar,16),
					new SqlParameter("@LegalTel", SqlDbType.VarChar,20),
					new SqlParameter("@LegalIDCardURL", SqlDbType.Text),
					new SqlParameter("@Captain", SqlDbType.NVarChar,16),
					new SqlParameter("@CaptainIDCardURL", SqlDbType.Text),
					new SqlParameter("@CaptainTel", SqlDbType.VarChar,20),
					new SqlParameter("@Email", SqlDbType.VarChar,128),
					new SqlParameter("@LicenceCode", SqlDbType.VarChar,50),
					new SqlParameter("@LicenceURL", SqlDbType.Text),
					new SqlParameter("@Permit", SqlDbType.VarChar,50),
					new SqlParameter("@PermitURL", SqlDbType.Text),
					new SqlParameter("@WinePermit", SqlDbType.VarChar,50),
					new SqlParameter("@WinePermitURL", SqlDbType.Text),
					new SqlParameter("@JoinMoney", SqlDbType.Decimal,5),
					new SqlParameter("@CreateTime", SqlDbType.VarChar,19),
					new SqlParameter("@ShopState", SqlDbType.TinyInt,1),
					new SqlParameter("@Note", SqlDbType.NText),
					new SqlParameter("@FacePhoto", SqlDbType.NVarChar,200),
					new SqlParameter("@ShopHours", SqlDbType.NText)};
             parameters[0].Value = model.ApplyID;
             parameters[1].Value = model.STitle;
             parameters[2].Value = model.Region;
             parameters[3].Value = model.Code;
             parameters[4].Value = model.KeyID;
             parameters[5].Value = model.POSID;
             parameters[6].Value = model.Bail;
             parameters[7].Value = model.ShopType;
             parameters[8].Value = model.PriceRole;
             parameters[9].Value = model.Address;
             parameters[10].Value = model.Legal;
             parameters[11].Value = model.LegalTel;
             parameters[12].Value = model.LegalIDCardURL;
             parameters[13].Value = model.Captain;
             parameters[14].Value = model.CaptainIDCardURL;
             parameters[15].Value = model.CaptainTel;
             parameters[16].Value = model.Email;
             parameters[17].Value = model.LicenceCode;
             parameters[18].Value = model.LicenceURL;
             parameters[19].Value = model.Permit;
             parameters[20].Value = model.PermitURL;
             parameters[21].Value = model.WinePermit;
             parameters[22].Value = model.WinePermitURL;
             parameters[23].Value = model.JoinMoney;
             parameters[24].Value = model.CreateTime;
             parameters[25].Value = model.ShopState;
             parameters[26].Value = model.Note;
             parameters[27].Value = model.FacePhoto;
             parameters[28].Value = model.ShopHours;

             object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
             if (obj == null)
             {
                 return 0;
             }
             else
             {
                 return Convert.ToInt32(obj);
             }
         }
         /// <summary>
         /// 更新一条数据
         /// </summary>
         public bool Update(ST_Shop model)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("update ST_Shop set ");
             strSql.Append("ApplyID=@ApplyID,");
             strSql.Append("STitle=@STitle,");
             strSql.Append("Region=@Region,");
             strSql.Append("Code=@Code,");
             strSql.Append("KeyID=@KeyID,");
             strSql.Append("POSID=@POSID,");
             strSql.Append("Bail=@Bail,");
             strSql.Append("ShopType=@ShopType,");
             strSql.Append("PriceRole=@PriceRole,");
             strSql.Append("Address=@Address,");
             strSql.Append("Legal=@Legal,");
             strSql.Append("LegalTel=@LegalTel,");
             strSql.Append("LegalIDCardURL=@LegalIDCardURL,");
             strSql.Append("Captain=@Captain,");
             strSql.Append("CaptainIDCardURL=@CaptainIDCardURL,");
             strSql.Append("CaptainTel=@CaptainTel,");
             strSql.Append("Email=@Email,");
             strSql.Append("LicenceCode=@LicenceCode,");
             strSql.Append("LicenceURL=@LicenceURL,");
             strSql.Append("Permit=@Permit,");
             strSql.Append("PermitURL=@PermitURL,");
             strSql.Append("WinePermit=@WinePermit,");
             strSql.Append("WinePermitURL=@WinePermitURL,");
             strSql.Append("JoinMoney=@JoinMoney,");
             strSql.Append("CreateTime=@CreateTime,");
             strSql.Append("ShopState=@ShopState,");
             strSql.Append("Note=@Note,");
             strSql.Append("FacePhoto=@FacePhoto,");
             strSql.Append("ShopHours=@ShopHours");
             strSql.Append(" where ID=@ID");
             SqlParameter[] parameters = {
					new SqlParameter("@ApplyID", SqlDbType.Int,4),
					new SqlParameter("@STitle", SqlDbType.NVarChar,64),
					new SqlParameter("@Region", SqlDbType.VarChar,6),
					new SqlParameter("@Code", SqlDbType.VarChar,14),
					new SqlParameter("@KeyID", SqlDbType.VarChar,14),
					new SqlParameter("@POSID", SqlDbType.VarChar,20),
					new SqlParameter("@Bail", SqlDbType.Decimal,5),
					new SqlParameter("@ShopType", SqlDbType.TinyInt,1),
					new SqlParameter("@PriceRole", SqlDbType.Int,4),
					new SqlParameter("@Address", SqlDbType.NVarChar,256),
					new SqlParameter("@Legal", SqlDbType.NVarChar,16),
					new SqlParameter("@LegalTel", SqlDbType.VarChar,20),
					new SqlParameter("@LegalIDCardURL", SqlDbType.Text),
					new SqlParameter("@Captain", SqlDbType.NVarChar,16),
					new SqlParameter("@CaptainIDCardURL", SqlDbType.Text),
					new SqlParameter("@CaptainTel", SqlDbType.VarChar,20),
					new SqlParameter("@Email", SqlDbType.VarChar,128),
					new SqlParameter("@LicenceCode", SqlDbType.VarChar,50),
					new SqlParameter("@LicenceURL", SqlDbType.Text),
					new SqlParameter("@Permit", SqlDbType.VarChar,50),
					new SqlParameter("@PermitURL", SqlDbType.Text),
					new SqlParameter("@WinePermit", SqlDbType.VarChar,50),
					new SqlParameter("@WinePermitURL", SqlDbType.Text),
					new SqlParameter("@JoinMoney", SqlDbType.Decimal,5),
					new SqlParameter("@CreateTime", SqlDbType.VarChar,19),
					new SqlParameter("@ShopState", SqlDbType.TinyInt,1),
					new SqlParameter("@Note", SqlDbType.NText),
					new SqlParameter("@FacePhoto", SqlDbType.NVarChar,200),
					new SqlParameter("@ShopHours", SqlDbType.NText),
					new SqlParameter("@ID", SqlDbType.Int,4)};
             parameters[0].Value = model.ApplyID;
             parameters[1].Value = model.STitle;
             parameters[2].Value = model.Region;
             parameters[3].Value = model.Code;
             parameters[4].Value = model.KeyID;
             parameters[5].Value = model.POSID;
             parameters[6].Value = model.Bail;
             parameters[7].Value = model.ShopType;
             parameters[8].Value = model.PriceRole;
             parameters[9].Value = model.Address;
             parameters[10].Value = model.Legal;
             parameters[11].Value = model.LegalTel;
             parameters[12].Value = model.LegalIDCardURL;
             parameters[13].Value = model.Captain;
             parameters[14].Value = model.CaptainIDCardURL;
             parameters[15].Value = model.CaptainTel;
             parameters[16].Value = model.Email;
             parameters[17].Value = model.LicenceCode;
             parameters[18].Value = model.LicenceURL;
             parameters[19].Value = model.Permit;
             parameters[20].Value = model.PermitURL;
             parameters[21].Value = model.WinePermit;
             parameters[22].Value = model.WinePermitURL;
             parameters[23].Value = model.JoinMoney;
             parameters[24].Value = model.CreateTime;
             parameters[25].Value = model.ShopState;
             parameters[26].Value = model.Note;
             parameters[27].Value = model.FacePhoto;
             parameters[28].Value = model.ShopHours;
             parameters[29].Value = model.ID;

             int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
             if (rows > 0)
             {
                 return true;
             }
             else
             {
                 return false;
             }
         }

         /// <summary>
         /// 删除一条数据
         /// </summary>
         public bool Delete(int ID)
         {

             StringBuilder strSql = new StringBuilder();
             strSql.Append("delete from ST_Shop ");
             strSql.Append(" where ID=@ID");
             SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
             parameters[0].Value = ID;

             int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
             if (rows > 0)
             {
                 return true;
             }
             else
             {
                 return false;
             }
         }
         /// <summary>
         /// 批量删除数据
         /// </summary>
         public bool DeleteList(string IDlist)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("delete from ST_Shop ");
             strSql.Append(" where ID in (" + IDlist + ")  ");
             int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
             if (rows > 0)
             {
                 return true;
             }
             else
             {
                 return false;
             }
         }


         /// <summary>
         /// 得到一个对象实体
         /// </summary>
         public ST_Shop GetModel(int ID)
         {

             StringBuilder strSql = new StringBuilder();
             strSql.Append("select  top 1 ID,ApplyID,STitle,Region,Code,KeyID,POSID,Bail,ShopType,PriceRole,Address,Legal,LegalTel,LegalIDCardURL,Captain,CaptainIDCardURL,CaptainTel,Email,LicenceCode,LicenceURL,Permit,PermitURL,WinePermit,WinePermitURL,JoinMoney,CreateTime,ShopState,Note,FacePhoto,ShopHours from ST_Shop ");
             strSql.Append(" where ID=@ID");
             SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
             parameters[0].Value = ID;

             ST_Shop model = new ST_Shop();
             DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
             if (ds.Tables[0].Rows.Count > 0)
             {
                 return DataRowToModel(ds.Tables[0].Rows[0]);
             }
             else
             {
                 return null;
             }
         }


         /// <summary>
         /// 得到一个对象实体
         /// </summary>
         public ST_Shop DataRowToModel(DataRow row)
         {
             ST_Shop model = new ST_Shop();
             if (row != null)
             {
                 if (row["ID"] != null && row["ID"].ToString() != "")
                 {
                     model.ID = int.Parse(row["ID"].ToString());
                 }
                 if (row["ApplyID"] != null && row["ApplyID"].ToString() != "")
                 {
                     model.ApplyID = int.Parse(row["ApplyID"].ToString());
                 }
                 if (row["STitle"] != null)
                 {
                     model.STitle = row["STitle"].ToString();
                 }
                 if (row["Region"] != null)
                 {
                     model.Region = row["Region"].ToString();
                 }
                 if (row["Code"] != null)
                 {
                     model.Code = row["Code"].ToString();
                 }
                 if (row["KeyID"] != null)
                 {
                     model.KeyID = row["KeyID"].ToString();
                 }
                 if (row["POSID"] != null)
                 {
                     model.POSID = row["POSID"].ToString();
                 }
                 if (row["Bail"] != null && row["Bail"].ToString() != "")
                 {
                     model.Bail = decimal.Parse(row["Bail"].ToString());
                 }
                 if (row["ShopType"] != null && row["ShopType"].ToString() != "")
                 {
                     model.ShopType = int.Parse(row["ShopType"].ToString());
                 }
                 if (row["PriceRole"] != null && row["PriceRole"].ToString() != "")
                 {
                     model.PriceRole = int.Parse(row["PriceRole"].ToString());
                 }
                 if (row["Address"] != null)
                 {
                     model.Address = row["Address"].ToString();
                 }
                 if (row["Legal"] != null)
                 {
                     model.Legal = row["Legal"].ToString();
                 }
                 if (row["LegalTel"] != null)
                 {
                     model.LegalTel = row["LegalTel"].ToString();
                 }
                 if (row["LegalIDCardURL"] != null)
                 {
                     model.LegalIDCardURL = row["LegalIDCardURL"].ToString();
                 }
                 if (row["Captain"] != null)
                 {
                     model.Captain = row["Captain"].ToString();
                 }
                 if (row["CaptainIDCardURL"] != null)
                 {
                     model.CaptainIDCardURL = row["CaptainIDCardURL"].ToString();
                 }
                 if (row["CaptainTel"] != null)
                 {
                     model.CaptainTel = row["CaptainTel"].ToString();
                 }
                 if (row["Email"] != null)
                 {
                     model.Email = row["Email"].ToString();
                 }
                 if (row["LicenceCode"] != null)
                 {
                     model.LicenceCode = row["LicenceCode"].ToString();
                 }
                 if (row["LicenceURL"] != null)
                 {
                     model.LicenceURL = row["LicenceURL"].ToString();
                 }
                 if (row["Permit"] != null)
                 {
                     model.Permit = row["Permit"].ToString();
                 }
                 if (row["PermitURL"] != null)
                 {
                     model.PermitURL = row["PermitURL"].ToString();
                 }
                 if (row["WinePermit"] != null)
                 {
                     model.WinePermit = row["WinePermit"].ToString();
                 }
                 if (row["WinePermitURL"] != null)
                 {
                     model.WinePermitURL = row["WinePermitURL"].ToString();
                 }
                 if (row["JoinMoney"] != null && row["JoinMoney"].ToString() != "")
                 {
                     model.JoinMoney = decimal.Parse(row["JoinMoney"].ToString());
                 }
                 if (row["CreateTime"] != null)
                 {
                     model.CreateTime = row["CreateTime"].ToString();
                 }
                 if (row["ShopState"] != null && row["ShopState"].ToString() != "")
                 {
                     model.ShopState = int.Parse(row["ShopState"].ToString());
                 }
                 if (row["Note"] != null)
                 {
                     model.Note = row["Note"].ToString();
                 }
                 if (row["FacePhoto"] != null)
                 {
                     model.FacePhoto = row["FacePhoto"].ToString();
                 }
                 if (row["ShopHours"] != null)
                 {
                     model.ShopHours = row["ShopHours"].ToString();
                 }
             }
             return model;
         }

         /// <summary>
         /// 获得数据列表
         /// </summary>
         public DataSet GetList(string strWhere)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select ID,ApplyID,STitle,Region,Code,KeyID,POSID,Bail,ShopType,PriceRole,Address,Legal,LegalTel,LegalIDCardURL,Captain,CaptainIDCardURL,CaptainTel,Email,LicenceCode,LicenceURL,Permit,PermitURL,WinePermit,WinePermitURL,JoinMoney,CreateTime,ShopState,Note,FacePhoto,ShopHours ");
             strSql.Append(" FROM ST_Shop ");
             if (strWhere.Trim() != "")
             {
                 strSql.Append(" where " + strWhere);
             }
             return DbHelperSQL.Query(strSql.ToString());
         }

         /// <summary>
         /// 获得前几行数据
         /// </summary>
         public DataSet GetList(int Top, string strWhere, string filedOrder)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select ");
             if (Top > 0)
             {
                 strSql.Append(" top " + Top.ToString());
             }
             strSql.Append(" ID,ApplyID,STitle,Region,Code,KeyID,POSID,Bail,ShopType,PriceRole,Address,Legal,LegalTel,LegalIDCardURL,Captain,CaptainIDCardURL,CaptainTel,Email,LicenceCode,LicenceURL,Permit,PermitURL,WinePermit,WinePermitURL,JoinMoney,CreateTime,ShopState,Note,FacePhoto,ShopHours ");
             strSql.Append(" FROM ST_Shop ");
             if (strWhere.Trim() != "")
             {
                 strSql.Append(" where " + strWhere);
             }
             strSql.Append(" order by " + filedOrder);
             return DbHelperSQL.Query(strSql.ToString());
         }

         /// <summary>
         /// 获取记录总数
         /// </summary>
         public int GetRecordCount(string strWhere)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select count(1) FROM ST_Shop ");
             if (strWhere.Trim() != "")
             {
                 strSql.Append(" where " + strWhere);
             }
             object obj = DbHelperSQL.GetSingle(strSql.ToString());
             if (obj == null)
             {
                 return 0;
             }
             else
             {
                 return Convert.ToInt32(obj);
             }
         }
         /// <summary>
         /// 分页获取数据列表
         /// </summary>
         public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("SELECT * FROM ( ");
             strSql.Append(" SELECT ROW_NUMBER() OVER (");
             if (!string.IsNullOrEmpty(orderby.Trim()))
             {
                 strSql.Append("order by T." + orderby);
             }
             else
             {
                 strSql.Append("order by T.ID desc");
             }
             strSql.Append(")AS Row, T.*  from ST_Shop T ");
             if (!string.IsNullOrEmpty(strWhere.Trim()))
             {
                 strSql.Append(" WHERE " + strWhere);
             }
             strSql.Append(" ) TT");
             strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
             return DbHelperSQL.Query(strSql.ToString());
         }

         #endregion  BasicMethod
		#region  MethodEx
         /// <summary>
         /// 获取门店店长信息
         /// </summary>
         public DataSet GetShopList(int InvestorID)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select  (select [STitle] from [dbo].[ST_Shop] where [ID]=[ShopID] ) as shopName,[ShopID],[TrueName],[MobilePhone],[Address] from [Permission].[User] as u inner join [dbo].[ST_ShopUser] as su on su.[ShopUserID]=u.[UserID] ");
             strSql.Append(" where u.[status]=1 and su.[ShopUserType]=1 and [ShopID] in(select [ShopID] from [GX_InvestorShop] where [InvestorID]=" + InvestorID + ")");
            
             return DbHelperSQL.Query(strSql.ToString());
         }
        /// <summary>
        /// 获取门店员工数
        /// </summary>
        /// <param name="InvestorID"></param>
        /// <returns></returns>
         public DataSet GetShopListNum(int InvestorID)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append(" select  [ShopID],count(*) as userNum from [Permission].[User] as u inner join [dbo].[ST_ShopUser] as su on su.[ShopUserID]=u.[UserID] ");
             strSql.Append(" where u.[status]=1  and [ShopID] in(select [ShopID] from [GX_InvestorShop] where [InvestorID]=" + InvestorID + ") group by [ShopID]");

             return DbHelperSQL.Query(strSql.ToString());
         }

         /// <summary>
         /// 获取门店营业信息
         /// </summary>
         public DataSet GetShopBusinessbyID(int InvestorID)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select a.ShopID,(select STitle from dbo.ST_Shop where id=a.ShopID)as STitle,(select ShopHours from dbo.ST_Shop where id=a.ShopID)as ShopHours,datediff (d,(select exedate from ST_InvestorPact where shopid=a.shopid and PactType=3),getdate())exedate from GX_InvestorShop a where a.InvestorID=" + InvestorID);
             return DbHelperSQL.Query(strSql.ToString());
         }
         /// <summary>
         /// 获得数据列表
         /// </summary>
         public DataSet GetListbyID(int InvestorID)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select [dbo].[ST_Shop].ID,[STitle],[Bail],[Legal],[LegalTel],[ShopHours],JoinMoney,[InvestorID],[ShopID] from [dbo].[ST_Shop],[dbo].[GX_InvestorShop]  ");
             strSql.Append(" where [dbo].[ST_Shop].ID=[dbo].[GX_InvestorShop].[ShopID] AND [dbo].[GX_InvestorShop].[InvestorID]=" + InvestorID);

             return DbHelperSQL.Query(strSql.ToString());
         }

		#endregion  MethodEx
	}
}

